/*
	This proc gives the transferred materail lots information on the day as well in between the date Ranges
	EXEC SP_TRANSFER_MATERIAL_LOT_REPORT '0','0','0','0','01/01/2010','05/19/2010'
*/
CREATE PROCEDURE SP_TRANSFER_MATERIAL_LOT_REPORT
(
	@PROCESS_TYPE		VARCHAR(50),
	@PROCESSSTEP_IDS	VARCHAR(100),
	@BASEPRODUCT_ID		VARCHAR(50),
	@VENDOR			    VARCHAR(100),
	@FROMDATE		    VARCHAR(50),
	@TODATE 		    VARCHAR(50)
)

 AS
 BEGIN
				
	CREATE TABLE #RESULT_TABLE ( [BASE PRODUCT] VARCHAR(50), [PART NUMBER] VARCHAR(50), [LOT NUMBER] VARCHAR(50),[FAB LOT NUMBER] VARCHAR(50),[VENDOR] VARCHAR(50),
						[PROCESS TYPE] VARCHAR(20), [TRANSFER DATE] DATETIME, [WAFER QTY] INT, [DIE QTY] INT, [WAFER IDS] VARCHAR(100), 
						[FROM DEPT] VARCHAR(20), [TO DEPT] VARCHAR(20), [FROM DEPT A/C] VARCHAR(35), [TO DEPT A/C] VARCHAR(35), 
						[COMMENTS] VARCHAR(100), [TRANSFER BY] VARCHAR(50) )
	
	DECLARE @SQLSTRING	NVARCHAR(4000)
	DECLARE @SQLSTRING1	NVARCHAR(4000)
		
	SET @SQLSTRING = N' INSERT INTO #RESULT_TABLE
						SELECT (SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
					CASE 
						WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
						WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
					END 
				),
				P.PART_NUMBER, LOT_NUMBER,FAB_LOT_NUMBER, L_VENDOR_NAME, PS_TYPE, TRANSFERRED_DATE,TRANSFERRED_WAFER_QTY,
				 TRANSFERRED_DIE_QTY,TRANSFERRED_WAFER_IDS, TRANSFERRED_FROM,TRANSFERRED_TO, NULL, NULL, Comments,ISNULL(TRANSFERRED_UPDATE_BY, TRANSFERRED_BY)
				FROM PRODUCT P, PROCESS_STEP PS, LOCATION L, LOT_TABLE LT, TRANSFERRED_LOTS TL
				WHERE P.PROCESSSTEP_ID=PS.PROCESSSTEP_ID AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID 
				AND LT.LOCATION_ID = L.LOCATION_ID AND LT.LOT_ID = TL.LOT_ID '
	
	SET @SQLSTRING1 = ''
	
	IF ( @PROCESS_TYPE IS NOT NULL AND @PROCESS_TYPE <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND PS.PS_TYPE = ''' +  @PROCESS_TYPE + '''' 
	END
		
	IF ( @PROCESSSTEP_IDS IS NOT NULL AND @PROCESSSTEP_IDS <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND LT.PROCESSSTEP_ID = ''' +  @PROCESSSTEP_IDS + '''' 
	END 
	
	IF ( @BASEPRODUCT_ID IS NOT NULL AND @BASEPRODUCT_ID <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND P_PARENT_PRODUCT_ID =  ''' +  @BASEPRODUCT_ID + '''' 
	END
	
	IF ( @VENDOR IS NOT NULL AND @VENDOR <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' and L.VENDOR_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING (  ''' + @VENDOR + '''' +') )'
	END

	SET @SQLSTRING1 = @SQLSTRING1 + ' AND TL.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@FROMDATE, 101)+''' AND '''+CONVERT(VARCHAR,@TODATE, 101)+''' '	
	
	--PRINT @SQLSTRING + @SQLSTRING1

	EXEC (@SQLSTRING + @SQLSTRING1)
	
	SELECT * FROM #RESULT_TABLE Order BY [BASE PRODUCT], [PART NUMBER], [TRANSFER DATE]
END